package com.ray.ba.starter.code.generator.util;


import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.zaxxer.hikari.HikariDataSource;
import lombok.experimental.UtilityClass;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;

/**
 * DbUtil
 *
 * @author ray
 * @date 2020/4/28
 */
@Slf4j
@UtilityClass
public class DbUtil {
    private final String LIST_TABLE_SQL = "select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables where table_schema = (select database()) %s order by table_name";
    private final String LIST_COLUMN_SQL = "select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns where table_name = ? and table_schema = (select database()) order by ordinal_position";
    private final String COUNT_SQL = "select count(1) from (%s) tmp";
    private final String PAGE_FRAGMENT_SQL = " limit ?,?";


    public HikariDataSource getDataSource(String url, String username, String password) {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;
    }

    public List<Entity> listTable(DataSource dataSource, int pageNum, int pageSize) throws SQLException {
        String sql = String.format(LIST_TABLE_SQL, "");
        return Db.use(dataSource).query(sql + PAGE_FRAGMENT_SQL, (pageNum - 1) * pageSize, pageSize);
    }

    public List<Entity> queryTableByTableNameLike(DataSource dataSource, String tableName, int pageNum, int pageSize) throws SQLException {
        String whereClauseItem = "and table_name like concat('%', ?, '%')";
        String sql = String.format(LIST_TABLE_SQL, whereClauseItem);
        return Db.use(dataSource).query(sql + PAGE_FRAGMENT_SQL, tableName, (pageNum - 1) * pageSize, pageSize);
    }

    public BigDecimal countTableByTableNameLike(DataSource dataSource, String tableName) throws SQLException {
        String whereClauseItem = "and table_name like concat('%', ?, '%')";
        String sql = String.format(LIST_TABLE_SQL, whereClauseItem);
        sql = String.format(COUNT_SQL, sql);
        return (BigDecimal) Db.use(dataSource).queryNumber(sql, tableName);
    }

    public Entity queryTableByTableNameEquals(DataSource dataSource, String tableName) throws SQLException {
        String whereClauseItem = "and table_name = ?";
        String sql = String.format(LIST_TABLE_SQL, whereClauseItem);
        return Db.use(dataSource).queryOne(sql, tableName);
    }

    public List<Entity> queryColumnByTableName(DataSource dataSource, String tableName) throws SQLException {
        return Db.use(dataSource).query(LIST_COLUMN_SQL, tableName);
    }


}
